package cn.edu.jju.psytest.persist;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;

import cn.edu.jju.psytest.model.entity.Student;
import cn.edu.jju.psytest.model.entity.Teacher;
import cn.edu.jju.psytest.model.entity.User;
import cn.edu.jju.psytest.model.vo.QueriedStuInfoVo;
import cn.edu.jju.psytest.model.vo.QueriedStuTRVo;
import cn.edu.jju.psytest.model.vo.QueriedTeacherInfoVo;
import cn.edu.jju.psytest.model.vo.QueryStuInfoCriteriaVo;
import cn.edu.jju.psytest.model.vo.QueryStuTRCriteriaVo;
import cn.edu.jju.psytest.model.vo.QueryTeacherCriteriaVo;
import cn.edu.jju.psytest.model.vo.TestResultVo;
import cn.edu.jju.psytest.persist.util.QuizRecordUtil;

@SuppressWarnings("restriction")
@Repository
public class UserDao {

	private HibernateTemplate hibernateTemplate;

	public User loadUser(String userId) throws DataAccessException {
		return hibernateTemplate.get(User.class, userId);
	}

	public void saveUser(User user) {
		hibernateTemplate.save(user);
	}

	public void deleteTeacherById(String teacherId) {
		User u = hibernateTemplate.get(User.class, teacherId);
		Teacher t = hibernateTemplate.get(Teacher.class, teacherId);
		hibernateTemplate.delete(u);
		hibernateTemplate.delete(t);
	}

	public void saveDetailInfoOfStudentUser(Student student) {
		hibernateTemplate.save(student);
	}

	public void saveDetailInfoOfTeacherUser(Teacher teacher) {
		hibernateTemplate.save(teacher);
	}

	public Student getDetailInfoOfStudentUser(String studentId) {
		return hibernateTemplate.get(Student.class, studentId);
	}

	public Teacher getDetailInfoOfTeacherUser(String teacherId) {
		return hibernateTemplate.get(Teacher.class, teacherId);
	}

	public List<QueriedStuTRVo> queryStuTestResultByCriteria(
			QueryStuTRCriteriaVo queryCriteria) {
		String selectSql = "select studentId,name,secondaryCollege,major,clazz from Student";
		boolean hasWhere = false;

		/**
		 * secondaryCollege
		 */
		if (!queryCriteria.getSecondaryCollege().equals("-1")) {
			selectSql += " Where secondaryCollege = '"
					+ queryCriteria.getSecondaryCollege() + "'";
			hasWhere = true;
		}

		/**
		 * Major
		 */
		if (!queryCriteria.getMajor().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where major = '" + queryCriteria.getMajor()
						+ "'";
			} else {
				selectSql += " and major = '" + queryCriteria.getMajor() + "'";
			}
			hasWhere = true;
		}

		/**
		 * Clazz
		 */
		if (!queryCriteria.getClazz().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where clazz = '"
						+ queryCriteria.getClazz().toUpperCase() + "'";
			} else {
				selectSql += " and clazz = '"
						+ queryCriteria.getClazz().toUpperCase() + "'";
			}
			hasWhere = true;
		}

		/**
		 * Sid
		 */
		if (!queryCriteria.getSno().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where studentId = '" + queryCriteria.getSno()
						+ "'";
			} else {
				selectSql += " and studentId = '" + queryCriteria.getSno()
						+ "'";
			}
			hasWhere = true;
		}

		/**
		 * Name
		 */
		if (!queryCriteria.getName().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where name = '" + queryCriteria.getName() + "'";
			} else {
				selectSql += " and name = '" + queryCriteria.getName() + "'";
			}
			hasWhere = true;
		}

		/**
		 * Gender
		 */
		if (!queryCriteria.getGender().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where gender = '" + queryCriteria.getGender()
						+ "'";
			} else {
				selectSql += " and gender = '" + queryCriteria.getGender()
						+ "'";
			}
			hasWhere = true;
		}

		/**
		 * Age
		 */
		if (!queryCriteria.getAge().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where age = '" + queryCriteria.getAge() + "'";
			} else {
				selectSql += " and age = '" + queryCriteria.getAge() + "'";
			}
			hasWhere = true;
		}

		List<?> queriedList = hibernateTemplate.find(selectSql);
		Iterator<?> iterator = queriedList.iterator();

		List<QueriedStuTRVo> quVoList = new ArrayList<QueriedStuTRVo>();
		QueriedStuTRVo quVo = null;

		while (iterator.hasNext()) {
			Object[] objs = (Object[]) iterator.next();
			TestResultVo trVo = getTestResultVo(queryCriteria, (String) objs[0]);
			if (trVo != null) {
				quVo = new QueriedStuTRVo();
				quVo.setSno((String) objs[0]);
				quVo.setName((String) objs[1]);
				quVo.setSecondaryCollege((String) objs[2]);
				quVo.setMajor((String) objs[3]);
				quVo.setClazz((String) objs[4]);
				quVo.setTrVo(trVo);
				quVoList.add(quVo);
			}
		}
		return quVoList;
	}

	public List<QueriedStuInfoVo> queryStuInfoByCriteria(
			QueryStuInfoCriteriaVo queryCriteria) {
		String selectSql = "select studentId,name,gender,age,secondaryCollege,major,clazz,bornPlace,healthStatus,raiseMethod,willConsult,relativesHealth,havePessiThoughts from Student";
		boolean hasWhere = false;

		/**
		 * secondaryCollege
		 */
		if (!queryCriteria.getSecondaryCollege().equals("-1")) {
			selectSql += " Where secondaryCollege = '"
					+ queryCriteria.getSecondaryCollege() + "'";
			hasWhere = true;
		}

		/**
		 * Major
		 */
		if (!queryCriteria.getMajor().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where major = '" + queryCriteria.getMajor()
						+ "'";
			} else {
				selectSql += " and major = '" + queryCriteria.getMajor() + "'";
			}
			hasWhere = true;
		}

		/**
		 * Clazz
		 */
		if (!queryCriteria.getClazz().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where clazz = '"
						+ queryCriteria.getClazz().toUpperCase() + "'";
			} else {
				selectSql += " and clazz = '"
						+ queryCriteria.getClazz().toUpperCase() + "'";
			}
			hasWhere = true;
		}

		/**
		 * Sid
		 */
		if (!queryCriteria.getSno().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where studentId = '" + queryCriteria.getSno()
						+ "'";
			} else {
				selectSql += " and studentId = '" + queryCriteria.getSno()
						+ "'";
			}
			hasWhere = true;
		}

		/**
		 * Name
		 */
		if (!queryCriteria.getName().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where name = '" + queryCriteria.getName() + "'";
			} else {
				selectSql += " and name = '" + queryCriteria.getName() + "'";
			}
			hasWhere = true;
		}

		/**
		 * Gender
		 */
		if (!queryCriteria.getGender().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where gender = '" + queryCriteria.getGender()
						+ "'";
			} else {
				selectSql += " and gender = '" + queryCriteria.getGender()
						+ "'";
			}
			hasWhere = true;
		}

		/**
		 * Age
		 */
		if (!queryCriteria.getAge().equals("-1")) {
			if (!hasWhere) {
				selectSql += " Where age = '" + queryCriteria.getAge() + "'";
			} else {
				selectSql += " and age = '" + queryCriteria.getAge() + "'";
			}
			hasWhere = true;
		}

		List<?> queriedList = hibernateTemplate.find(selectSql);
		Iterator<?> iterator = queriedList.iterator();

		List<QueriedStuInfoVo> qsiVoList = new ArrayList<QueriedStuInfoVo>();
		QueriedStuInfoVo qsiVo = null;
		while (iterator.hasNext()) {
			Object[] objs = (Object[]) iterator.next();
			qsiVo = new QueriedStuInfoVo();
			qsiVo.setSno((String) objs[0]);
			qsiVo.setName((String) objs[1]);
			qsiVo.setGender((String) objs[2]);
			qsiVo.setAge((Integer) objs[3]);
			qsiVo.setSecondaryCollege((String) objs[4]);
			qsiVo.setMajor((String) objs[5]);
			qsiVo.setClazz((String) objs[6]);
			qsiVo.setBornPlace((String) objs[7]);
			qsiVo.setHealthStatus((String) objs[8]);
			qsiVo.setRaiseMethod((String) objs[9]);
			qsiVo.setRelativesHealth((String) objs[10]);
			qsiVo.setWillConsult((String) objs[11]);
			qsiVo.setHavePessiThoughts((String) objs[12]);
			qsiVoList.add(qsiVo);
		}
	
		return qsiVoList;
	}

	public List<QueriedTeacherInfoVo> queryTeacherInfoByCriteria(
			QueryTeacherCriteriaVo queryCriteria) {
		String selectSql = "select t.teacherId,u.password,t.privilege from Teacher as t,User as u where t.teacherId = u.userId";

		/**
		 * teacherId
		 */
		if (!queryCriteria.getTeacherId().equals("-1")) {
			selectSql += " And t.teacherId = '"
					+ queryCriteria.getTeacherId() + "'";
		}
		
		/**
		 * privilege
		 */
		if (queryCriteria.getPrivilege() != -1) {
			selectSql += " And t.privilege = '"
					+ queryCriteria.getPrivilege() + "'";
		}


		List<?> queriedList = hibernateTemplate.find(selectSql);
		Iterator<?> iterator = queriedList.iterator();

		List<QueriedTeacherInfoVo> qtVoList = new ArrayList<QueriedTeacherInfoVo>();
		QueriedTeacherInfoVo qtVo = null;
		while (iterator.hasNext()) {
			Object[] objs = (Object[]) iterator.next();
			qtVo = new QueriedTeacherInfoVo();
			qtVo.setTeacherId((String) objs[0]);
			qtVo.setPassword((String) objs[1]);
			qtVo.setPrivilege((Integer) objs[2]);
			qtVoList.add(qtVo);
		}
	
		return qtVoList;
	}
	
	/**
	 * Private Methods
	 */
	private TestResultVo getTestResultVo(QueryStuTRCriteriaVo queryCriteria,
			String sid) {
		String selectSql = "select answers from QuizRecord where studentId = '"
				+ sid + "'";
		if (!queryCriteria.getTestTime().equals("-1")) {
			selectSql += " and createdDate = '" + queryCriteria.getTestTime()
					+ "'";
		}
		List<?> queriedList = hibernateTemplate.find(selectSql);
		String answerS = (String) queriedList.get(0);

		/**
		 * question
		 */
		if (!queryCriteria.getQuestion().equals("-1")) {
			String[] answers = answerS.split(":");
			int index = Integer.parseInt(queryCriteria.getQuestion()) - 1;

			int answer = Integer.parseInt(answers[index]);
			String option = queryCriteria.getQuestionOption();
			int value = Integer.parseInt(queryCriteria.getQuestionValue());

			if (option.equals("mt")) {
				if (answer <= value) {
					return null;
				}
			} else if (option.equals("lt")) {
				if (answer >= value) {
					return null;
				}
			} else if (option.equals("eq")) {
				if (answer != value) {
					return null;
				}
			}
		}

		TestResultVo trVo = QuizRecordUtil.getTestResult(answerS);

		/**
		 * totalScore
		 */
		if (!queryCriteria.getLowestTotalScore().equals("-1")) {
			int ts = Integer.parseInt(trVo.getTotalScore());
			int lts = Integer.parseInt(queryCriteria.getLowestTotalScore());
			if (ts < lts) {
				return null;
			}
		}

		/**
		 * nopi
		 */
		if (!queryCriteria.getLowestNopi().equals("-1")) {
			int nopi = Integer.parseInt(trVo.getNumOfPositiveItems());
			int lnopi = Integer.parseInt(queryCriteria.getLowestNopi());
			if (nopi < lnopi) {
				return null;
			}
		}

		/**
		 * factor
		 */
		if (!queryCriteria.getFactor().equals("-1")) {
			Map<String, String> fas = trVo.getFactorsAverageScore();

			String factor = fas.get(queryCriteria.getFactor());
			String factorOption = queryCriteria.getFactorOption();
			float factorValue = Float
					.parseFloat(queryCriteria.getFactorValue());

			if (factor != null) {
				float f = Float.parseFloat(factor);
				if (factorOption.equals("mt")) {
					if (f <= factorValue) {
						return null;
					}
				} else if (factorOption.equals("lt")) {
					if (f >= factorValue) {
						return null;
					}
				} else if (factorOption.equals("eq")) {
					if (f != factorValue) {
						return null;
					}
				}
			} else {
				Iterator<String> iterator = fas.values().iterator();
				while (iterator.hasNext()) {
					float f = Float.parseFloat(iterator.next());
					if (factorOption.equals("mt")) {
						if (f <= factorValue) {
							return null;
						}
					} else if (factorOption.equals("lt")) {
						if (f >= factorValue) {
							return null;
						}
					} else if (factorOption.equals("eq")) {
						if (f != factorValue) {
							return null;
						}
					}
				}
			}
		}
		return trVo;
	}

	/**
	 * Getters And Setters
	 */
	public HibernateTemplate getHibernateTemplate() {
		return hibernateTemplate;
	}

	@Resource
	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}
}
